package example;

import db.domain.Org;
import db.domain.User;
import org.junit.jupiter.api.Test;
import sf.database.dialect.DialectUtils;
import sf.database.jdbc.sql.SQLContext;
import sf.database.jdbc.sql.SQLParameter;
import sf.database.util.SimpleSQLTemplate;
import sf.dsl.example.CaseBuilder;
import sf.dsl.example.DBOperator;
import sf.dsl.example.Example;
import sf.dsl.example.ExampleSQL;
import sf.dsl.example.OracleQuery;
import sf.dsl.example.Order;
import sf.dsl.example.OrderField;
import sf.dsl.example.QPI;
import sf.dsl.example.SQLExpressions;
import sf.dsl.example.SelectOpt;
import sf.dsl.example.SimpleField;
import sf.dsl.example.SimpleTable;
import sf.dsl.example.WindowFunction;
import sf.dsl.example.WithinGroup;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;

public class ExampleTest1 {
    @Test
    public void test1() {
        User u = new User();
        Example example = u.useQuery();
        example.orderBy(User.Field.id, User.Field.nicename);
        System.out.println(ExampleSQL.getSelectSQLContext(example, null, SelectOpt.all, true).getSql());
        StringBuilder sb = new StringBuilder();
        List<SQLParameter> list = new ArrayList<>();
        ExampleSQL.getSQLContextWhereClause(sb, list, example);
        System.out.println(sb);
        u = new User();
        example = u.useQuery();
        example.orderByDesc(User.Field.id, User.Field.nicename);
        System.out.println(ExampleSQL.getSelectSQLContext(example, null, SelectOpt.all, true).getSql());
        sb = new StringBuilder();
        list = new ArrayList<>();
        ExampleSQL.getSQLContextWhereClause(sb, list, example);
        System.out.println(sb);
        u = new User();
        example = u.useQuery();
        example.orderBy(User.Field.id, Order.ASC);
        example.orderBy(User.Field.nicename, Order.DESC);
        System.out.println(ExampleSQL.getSelectSQLContext(example, null, SelectOpt.all, true).getSql());
        sb = new StringBuilder();
        list = new ArrayList<>();
        ExampleSQL.getSQLContextWhereClause(sb, list, example);
        System.out.println(sb);
    }

    @Test
    public void test2() {
        User u = new User();
        Example example = u.useQuery();
        example.setDialect(DialectUtils.mysql);
        example.setWhereWrapper(true);
        example.getFromTables().get(0).as("c");
        example.selectProperties(User.Field.order).select("max(" + SimpleSQLTemplate.wrapperColumn(User.Field.order.c()) + ")");
        example.w(QPI.addCondition(SimpleSQLTemplate.wrapperColumn(User.Field.order.c()) + " > 1"));
        example.orderBy(User.Field.id, User.Field.nicename);
        SQLContext sqlContext = ExampleSQL.getSelectSQLContext(example, null, SelectOpt.all, false);
        System.out.println(sqlContext.getSql());
    }

    @Test
    public void test3() {
        List<Object> values = Arrays.asList(1, 2, 3, 5, 7);
        Object[] objArray = new Object[]{1, 18, 34};
        List<Object> objList = Arrays.asList(2, 16, 22);

        values = Collections.emptyList();
        objArray = new Object[]{};
        objList = Collections.emptyList();

        SimpleTable c = new SimpleTable(User.class);
        Example example = new Example(DialectUtils.mysql).from(c);
        example.setWhereWrapper(true);
        example.w(QPI.in(new SimpleField[]{c.c(User.Field.id), c.c(User.Field.order)}, values)).and()
                .w(QPI.notIn(new SimpleField[]{c.c(User.Field.id), c.c(User.Field.order)}, values)).and()
                .w(QPI.row(new SimpleField[]{c.c(User.Field.id), c.c(User.Field.age)}, DBOperator.EQUALS, objArray)).and()
                .w(QPI.row(new SimpleField[]{c.c(User.Field.id), c.c(User.Field.age)}, DBOperator.EQUALS, objList))
                .orderBy(User.Field.id, User.Field.nicename)
                .createCriteria().and().notIn(new SimpleField[]{c.c(User.Field.maps1), c.c(User.Field.age)}, values);
        SQLContext context = ExampleSQL.getSelectSQLContext(example, null, SelectOpt.all, false);
        System.out.println(context.getSql());
        System.out.println(context.getParas());
    }

    @Test
    public void test4() {
        SimpleTable c1 = new SimpleTable(User.class);
        WindowFunction wf = SQLExpressions.countDistinct(c1.c(User.Field.id))
                .over().partitionBy(c1.c(User.Field.id)).orderBy(new OrderField(c1.c(User.Field.id), Order.ASC));
        wf.as("cc");
        System.out.println(wf);
        WithinGroup wg = SQLExpressions.listagg(c1.c(User.Field.age), ",").orderBy(new OrderField(c1.c(User.Field.age), Order.ASC));
        wg.as("aa");
        System.out.println(wg);
        WindowFunction wr = SQLExpressions.countDistinct(c1.c(User.Field.id))
                .over().partitionBy(c1.c(User.Field.id)).orderBy(new OrderField(c1.c(User.Field.id), Order.ASC))
                .range().between().unboundedPreceding().currentRow();
        wr.as("dd");
        System.out.println(wr);
        Example e = new Example().select(c1.c(User.Field.age), wf, wg, wr).from(c1)
                .groupBy(c1.c(User.Field.age))
                .orderBy(c1.c(User.Field.age));
        SQLContext context = ExampleSQL.getSelectSQLContext(e, null, SelectOpt.all, true);
        System.out.println(context.getSql());
        System.out.println(context.getParas());
    }

    @Test
    public void testWindow() {
        SimpleTable c1 = new SimpleTable(User.class).schema("sxf_test").as("k");
        Example e = new Example();
        WindowFunction field1 = SQLExpressions.avg(c1.c(User.Field.age)).over().filter(c1.c(User.Field.age).ge(25), QPI.and(),
                        c1.c(User.Field.age).le(60))
                .orderBy(new OrderField(c1.c(User.Field.age), Order.DESC)).as("s1");
        WindowFunction field2 = SQLExpressions.rank().over().orderBy(new OrderField(c1.c(User.Field.age), Order.DESC)).as("s2");
        WindowFunction field3 = SQLExpressions.rowNumber().over().orderBy(new OrderField(c1.c(User.Field.age), Order.DESC)).as("s3");
        e.select(c1.c(User.Field.age), field1, field3).from(c1)
                .groupBy(c1.c(User.Field.age))
                .window("t1", field1).window("t2", field2)
                .orderBy(c1.c(User.Field.age));
        SQLContext context = ExampleSQL.getSelectSQLContext(e, null, SelectOpt.all, true);
        System.out.println(context.getSql());
        System.out.println(context.getParas());
    }

    @Test
    public void testOracle() {
        SimpleTable t = new SimpleTable(Org.class).as("k");
        Example e = new Example();
        e.select(t.all()).from(t).f(OracleQuery.startWith(), t.c(Org.Field.id).eq("1")
                , OracleQuery.connectByPrior(), t.c(Org.Field.id).eq(t.c(Org.Field.parentId)));
        SQLContext context = ExampleSQL.getSelectSQLContext(e, null, SelectOpt.all, true);
        System.out.println(context.getSql());
        System.out.println(context.getParas());
    }

    @Test
    public void testExpression() {
        SimpleTable t = new SimpleTable(Org.class).as("k");
        Example e = new Example();
        e.select(t.all()).from(t).f(OracleQuery.startWith(), t.c(Org.Field.id).eq("1")
                        , OracleQuery.connectByPrior(), t.c(Org.Field.id).eq(t.c(Org.Field.parentId)))
                .w(t.c(Org.Field.id).ge(QPI.expressionField("({0}-1)", t.c(Org.Field.id)))).and()
                .w(t.c(Org.Field.id).ge(QPI.expressionField("(aa-1)", new SimpleField[]{})));
        SQLContext context = ExampleSQL.getSelectSQLContext(e, null, SelectOpt.all, true);
        System.out.println(context.getSql());
        System.out.println(context.getParas());
    }

    @Test
    public void testCaseWhen() {
        SimpleTable c1 = new SimpleTable(User.class).as("k");
        Example e = new Example();
        SimpleField field1 = CaseBuilder.CASE(c1.c(User.Field.age))
                .when(QPI.raw("2")).then(QPI.raw("'ok1'"))
                .when(QPI.raw("3")).then(QPI.raw("'three'"))
                .otherwise("ss1").as("o");
        SimpleField field2 = CaseBuilder.CASE()
                .when(c1.c(User.Field.age).eq(QPI.raw("1"))).then(QPI.raw("'ok2'"))
                .when(c1.c(User.Field.age).eq(QPI.raw("3"))).then(QPI.raw("'three'"))
                .otherwise("ss2").as("k");

        e.select(c1.c(User.Field.age), field1, field2).from(c1)
                .w(QPI.columnInfo(c1.c(User.Field.nicename)), QPI.addCondition(" regexp "), QPI.add("^nn")).and()
                .w(QPI.row(new SimpleField[]{c1.c(User.Field.age), c1.c(User.Field.id)}, DBOperator.IN,
                        QPI.wrap(c1.c(User.Field.age), c1.c(User.Field.id))));
        SQLContext context = ExampleSQL.getSelectSQLContext(e, null, SelectOpt.all, true);
        System.out.println(context.getSql());
        System.out.println(context.getParas());
    }

    @Test
    public void test22() {
        SimpleField[] columns = new SimpleField[]{};
        System.out.println(columns.getClass().getComponentType() == SimpleField.class);
        System.out.println(SimpleField.class.getComponentType());
    }
}
